import mysql.connector
from docx import Document

def extract_true_false_questions(file_path):
    document = Document(file_path)
    questions = []
    current_question = None

    for para in document.paragraphs:
        text = para.text.strip()

        # 识别题目
        if text.startswith("Q:"):
            if current_question:
                questions.append(current_question)
            current_question = {
                'text': text[2:].strip(),
                'correct_answer': None
            }

        # 识别正确答案
        elif text.startswith("答案:"):
            if current_question:
                answer_text = text[3:].strip()
                if answer_text in ['对', '错']:
                    current_question['correct_answer'] = '对' if answer_text == '对' else '错'

    # 添加最后一个题目（如果存在）
    if current_question:
        questions.append(current_question)

    return questions

def insert_true_false_questions(exam_name, questions):
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="3306",  # 确保密码正确
        database="school",
        charset='utf8mb4'  # 确保使用 utf8mb4 字符集
    )
    cursor = conn.cursor()

    try:
        # 检查试卷是否已存在
        cursor.execute("SELECT id FROM exams WHERE name = %s", (exam_name,))
        result = cursor.fetchone()
        if result:
            exam_id = result[0]
        else:
            # 插入试卷信息
            sql = "INSERT INTO exams (name) VALUES (%s)"
            values = (exam_name,)
            cursor.execute(sql, values)
            exam_id = cursor.lastrowid

        for question in questions:
            # 检查该判断题是否已存在
            cursor.execute("""
                SELECT id FROM question2 
                WHERE exam_id = %s AND question_text = %s AND correct_answer = %s
            """, (exam_id, question['text'], question['correct_answer']))
            result = cursor.fetchone()
            if result:
                continue  # 如果该题目已存在，则跳过

            # 插入新的判断题
            sql = """
            INSERT INTO question2 (exam_id, question_text, correct_answer)
            VALUES (%s, %s, %s)
            """
            values = (
                exam_id,
                question['text'],
                question['correct_answer']
            )
            cursor.execute(sql, values)

        conn.commit()
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

# 使用示例
file_path = 'C:\\Users\\颜心怡\\Desktop\\判断题.docx'
exam_name = 'Sample True/False Exam'

# 提取题目和答案
questions = extract_true_false_questions(file_path)

# 将题目和答案上传到数据库
insert_true_false_questions(exam_name, questions)